package com.weddingee.dao.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.weddingee.dao.base.BaseDao;
import com.weddingee.exception.DBException;
import com.weddingee.exception.DaoException;
import com.weddingee.model.News;
import com.weddingee.model.User;
import com.weddingee.util.Constants;
import com.weddingee.util.cond.UserCondition;

public class UserDao extends BaseDao {

	public User add(User user)throws DaoException{
		Connection conn = null;
		PreparedStatement pstm  = null;
			try {
				conn = getConn();
				String sql = "insert into user(ID,USERNAME,PASSWORD,REALNAME,TYPE,EMAIL,SEX,HEAD_PIC,PROFILE_1,PROFILE_2,PROFILE_3,PROFILE_4,PROFILE_5,PROFILE_6,CREATE_TIME,VISIT_TIMES) values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
				pstm = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
				
				//NAME
				pstm.setString(1,  user.getUsername());
				//PASSWORD
				pstm.setString(2,  user.getPassword());
				//REALNAME
				pstm.setString(3,  user.getRealname());
				//TYPE
				pstm.setString(4,  user.getType());
				//EMAIL
				pstm.setString(5,  user.getEmail());
				//SEX
				pstm.setLong(6, user.getSex());	
				//PICURL
				pstm.setString(7, user.getHeadPic());
				//PROFILE_1 ~ PROFILE_6
				pstm.setString(8, user.getProfile1());
				pstm.setString(9, user.getProfile2());
				pstm.setString(10, user.getProfile3());
				pstm.setString(11, user.getProfile4());
				pstm.setString(12, user.getProfile5());
				pstm.setString(13, user.getProfile6());
				pstm.setTimestamp(14, new Timestamp(user.getCreateTime().getTime()));
				pstm.setLong(15, user.getVisitTimes());
				
				int res = pstm.executeUpdate();
				if( res > 0){
					ResultSet rset = pstm.getGeneratedKeys();
					rset.next();
					user.setUserId(rset.getLong(1));
					return user;
				}
				
				return null;
			} catch (Exception e) {
				e.printStackTrace();
				throw new DaoException("error");
			} finally{
				try {
					closeStatement(pstm);
					closeConn(conn);
				} catch (DBException e) {
					e.printStackTrace();
				}
			}
		
	}
	
	
	/**
	 * 按ID查用户
	 * 
	 * @param id
	 * @return
	 * @throws DaoException
	 */
	public User findByID(Long id) throws DaoException {
		Connection conn = null;
		PreparedStatement pstm = null;

		List<News> newsList = new ArrayList<News>();
		try {
			conn = getConn();
			String sql = "SELECT * FROM user as _u where _u.ID=?";
			pstm = conn.prepareStatement(sql);
			
			pstm.setLong(1,id);
			
			ResultSet rs = pstm.executeQuery();
			int i = 0;
			User user = null;
			while (rs.next()) {
				user = createUser(rs);
				
				return user;
			}
			
			return user;

		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("error", e);
		} finally {
			try {
				closeStatement(pstm);
				closeConn(conn);
			} catch (DBException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 按用户名查用户
	 * 
	 * @param username
	 * @return
	 * @throws DaoException
	 */
	public User findByUsername(String username) throws DaoException {
		Connection conn = null;
		PreparedStatement pstm = null;

		List<News> newsList = new ArrayList<News>();
		try {
			conn = getConn();
			String sql = "SELECT * FROM user as _u where _u.USERNAME=?";
			pstm = conn.prepareStatement(sql);
			
			pstm.setString(1,username);
			
			ResultSet rs = pstm.executeQuery();
			int i = 0;
			User user = null;
			while (rs.next()) {
				user = createUser(rs);
				return user;
			}
			
			return user;

		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("error", e);
		} finally {
			try {
				closeStatement(pstm);
				closeConn(conn);
			} catch (DBException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 按真实姓名查用户
	 * 
	 * @param username
	 * @return
	 * @throws DaoException
	 */
	public User findByRealname(String realname) throws DaoException {
		Connection conn = null;
		PreparedStatement pstm = null;

		List<News> newsList = new ArrayList<News>();
		try {
			conn = getConn();
			String sql = "SELECT * FROM user as _u where _u.REALNAME=?";
			pstm = conn.prepareStatement(sql);
			
			pstm.setString(1,realname);
			
			ResultSet rs = pstm.executeQuery();
			int i = 0;
			User user = null;
			while (rs.next()) {
				user = createUser(rs);
				return user;
			}
			
			return user;

		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("error", e);
		} finally {
			try {
				closeStatement(pstm);
				closeConn(conn);
			} catch (DBException e) {
				e.printStackTrace();
			}
		}
	}
	
	public List<User> listUser(UserCondition cond)throws DaoException{
		if( null == cond || 0 == cond.getCount() ){
			log.info("参数错误.");
			throw new DaoException("参数错误.");
		}
		Connection conn = null;
		PreparedStatement pstm = null;
		int page = cond.getPage();
		int count = cond.getCount();
		String type = cond.getType();
		
		try {
			List<User> userList = new ArrayList<User>();
			conn = getConn();
			String sql = null;
			if( cond.getType() == null ){
				sql = "SELECT * FROM user AS _u ORDER BY _u.CREATE_TIME asc LIMIT ?,?";
			}else{
				sql = "SELECT * FROM user AS _u WHERE _u.TYPE=? ORDER BY _u.CREATE_TIME asc LIMIT ?,?";
			}
			
			pstm = conn.prepareStatement(sql);
			
			if( cond.getType() == null ){
				//do nothing
				pstm.setInt(1, cond.getCount()*(cond.getPage()-1));
				pstm.setInt(2, cond.getCount());
			}else{
				pstm.setString(1,cond.getType());
				pstm.setInt(2, cond.getCount()*(cond.getPage()-1));
				pstm.setInt(3, cond.getCount());
			}
			
			
			ResultSet rs = pstm.executeQuery();
			User user = null;
			while (rs.next()) {
				user = createUser(rs);
				userList.add(user);
			}
			
			return userList;

		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("error", e);
		} finally {
			try {
				closeStatement(pstm);
				closeConn(conn);
			} catch (DBException e) {
				e.printStackTrace();
			}
		}
		
	}
	
	private User createUser(ResultSet rs)throws SQLException {
		if( null == rs){
			return null;
		}
		User user = new User();
		user.setUserId(rs.getLong("ID"));
		user.setPassword(rs.getString("PASSWORD"));
		user.setType(rs.getString("TYPE"));
		user.setUsername(rs.getString("USERNAME"));
		user.setRealname(rs.getString("REALNAME"));
		user.setHeadPic(rs.getString("HEAD_PIC"));
		user.setEmail(rs.getString("EMAIL"));
		user.setSex(rs.getLong("SEX"));
		user.setProfile1(rs.getString("PROFILE_1"));
		user.setProfile2(rs.getString("PROFILE_2"));
		user.setProfile3(rs.getString("PROFILE_3"));
		user.setProfile4(rs.getString("PROFILE_4"));
		user.setProfile5(rs.getString("PROFILE_5"));
		user.setProfile6(rs.getString("PROFILE_6"));
		user.setProfile7(rs.getString("PROFILE_7"));
		user.setIntroduce(rs.getString("INTRODUCE"));
		user.setCreateTime(rs.getDate("CREATE_TIME"));
		user.setVisitTimes(rs.getLong("VISIT_TIMES"));
		return user;
	}

	/**
	 * 删除用户
	 * @param id
	 */
	public void delete(Long id)throws DaoException{
		if( null == id){
			log.info("参数错误.id");
			throw new DaoException("参数错误.id");
		}
		
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = getConn();
			String sql = "DELETE FROM user where ID=?";
			pstm = conn.prepareStatement(sql);
			
			pstm.setLong(1, id);
			
			int changeNum = pstm.executeUpdate();
			log.info("删除了"+ changeNum +"条.");
			
		} catch (Exception e) {
			log.info("error",e);
			throw new DaoException("error", e);
		} finally {
			try {
				closeStatement(pstm);
				closeConn(conn);
			} catch (DBException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 添加一次访问次数
	 */
	public void addVisitTimes(Long uid){
		if( null == uid){
			log.info("参数错误.id");
			throw new DaoException("参数错误.id");
		}
		
		Connection conn = null;
		PreparedStatement pstm = null;
		
		try {
			conn = getConn();
			String sql = "UPDATE user set VISIT_TIMES=VISIT_TIMES+1 where ID=?";
			pstm = conn.prepareStatement(sql);
			
			pstm.setLong(1, uid);
			
			int changeNum = pstm.executeUpdate();
			log.info("更新了"+ changeNum +"条.");
			
		} catch (Exception e) {
			log.info("error",e);
			throw new DaoException("error", e);
		} finally {
			try {
				closeStatement(pstm);
				closeConn(conn);
			} catch (DBException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) {
		/*for(int i=1;i<800;i++){
			User user = new User();
			user.setUsername("TestUser" + i);
			user.setPassword("TestUser"+i);
			user.setRealname("TestReal"+i);
			user.setSex((long)i%2);
			user.setType( (i%6) == 0 ? 1: i%6);
			user.setEmail("Test" + i + "@test.com");
			user.setHeadPic("http://ww1.sinaimg.cn/bmiddle/62f87eb4jw1dtyl3eph0zj.jpg");
			user.setCreateTime(new Date());
			new UserDao().add(user);
		}*/
		/*for(int i=1;i<30;i++){
			new UserDao().delete((long)i);
		}*/
		
		/*User user = new UserDao().findByID(3L);
		System.out.println(user);*/
		
		
		/*User user = new UserDao().findByUsername("test2");
		System.out.println(user);*/
		
		/*User user = new UserDao().findByRealname("改革名字");
		System.out.println(user);*/
		
		/*UserCondition cond = new UserCondition();
		cond.setCount(3);
		cond.setType(2);
		cond.setPage(1);
		List<User> userList = new UserDao().listUser(cond);
		for(User u:userList){
			System.out.println(u);
		}*/
		
	}

	/**
	 * cond.type类型用户的数量
	 * @param cond
	 * @return
	 */
	public int listUserTotalCount(UserCondition cond) {
		if( null == cond || 0 == cond.getCount() ){
			log.info("参数错误.");
			throw new DaoException("参数错误.");
		}
		Connection conn = null;
		PreparedStatement pstm = null;
		int page = cond.getPage();
		int count = cond.getCount();
		String type = cond.getType();
		int totalCount = 0;
		try {
			List<User> userList = new ArrayList<User>();
			conn = getConn();
			String sql = null;
			if( cond.getType() == null ){
				sql = "SELECT count(*) FROM user AS _u";
			}else{
				sql = "SELECT count(*) FROM user AS _u WHERE _u.TYPE=?";
			}
			
			pstm = conn.prepareStatement(sql);
			
			if( cond.getType() == null ){
				//do nothing
			}else{
				pstm.setString(1,cond.getType());
			}
			
			ResultSet rs = pstm.executeQuery();
			
			while (rs.next()) {
				totalCount = rs.getInt(1);
			}
			
			return totalCount;

		} catch (Exception e) {
			e.printStackTrace();
			throw new DaoException("error", e);
		} finally {
			try {
				closeStatement(pstm);
				closeConn(conn);
			} catch (DBException e) {
				e.printStackTrace();
			}
		}
	}
}
